﻿using System;
using System.Data;
using System.Data.Common;
using GPSTrackingDBV2.Data;
using GPSTrackingDBV2.Data.SqlClient;
using GPSTrackingDBV2.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Collections.Specialized;
using System.Data.SqlClient;

namespace GPSTrackingDBV2Custom.Data
{
    public class DataRepository
    {
        public static string _connectionString = GPSTrackingDBV2.Data.DataRepository.ConnectionStrings["netTiersConnectionString"].ConnectionString;

        public class CatalogStatistics
        {
            public static DataTable GetAll(int CompanyID, DateTime beginDate, DateTime endDate)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Report_Catolog_GetStatisticsByItemView", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.String, CompanyID);
                database.AddInParameter(commandWrapper, "@BeginDate", DbType.DateTime, beginDate);
                database.AddInParameter(commandWrapper, "@EdDate", DbType.DateTime, endDate);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class EmailToSendStockByStocksProvider
        {
            public static void Insert(int emailToSendStockId, int companyId, int stockId, int reservedQuantity)
            {

                SqlDatabase database;
                DbCommand commandWrapper;

                try
                {
                    database = new SqlDatabase(_connectionString);
                    commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_EmailToSendStockByStocks_Insert", true);

                    database.AddInParameter(commandWrapper, "@EmailToSendStockId", DbType.Int64, emailToSendStockId);
                    database.AddInParameter(commandWrapper, "@CompanyId", DbType.Int64, companyId);
                    database.AddInParameter(commandWrapper, "@StockId", DbType.Int64, stockId);
                    database.AddInParameter(commandWrapper, "@ReservedQuantity", DbType.Int64, reservedQuantity);


                    Utility.ExecuteScalar(database, commandWrapper);
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo asociar los productos: " + e.Message);
                }
            }

        }


        public class VPCTrackDataProvider
        {
            public static DataTable GetVPCListByCompanyID(int CompanyID, DateTime DateTime)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCTrackData_Get_Counter_List", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.String, CompanyID);
                database.AddInParameter(commandWrapper, "@DateTime", DbType.DateTime, DateTime);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class StockSettingProvider
        {
            public static DataTable GetByCompanyId(int CompanyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockSetting_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.String, CompanyID);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int GetNextID(int companyId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.StockSetting_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.String, companyId);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

        }


        public class TrackDataLastLocationProvider
        {
            public static void UpdateOrInsert(TrackDataLastLocation TrackDataLastLocationEntity)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Track_Data_Last_Location_Custom_Update_Or_Insert", true);

                database.AddInParameter(commandWrapper, "@Imei", DbType.String, TrackDataLastLocationEntity.GpsImei);
                database.AddInParameter(commandWrapper, "@DateTime ", DbType.DateTime, TrackDataLastLocationEntity.DateTime);
                database.AddInParameter(commandWrapper, "@Latitude", DbType.Double, TrackDataLastLocationEntity.Latitude);
                database.AddInParameter(commandWrapper, "@Longitude", DbType.Double, TrackDataLastLocationEntity.Longitude);
                database.AddInParameter(commandWrapper, "@Heading", DbType.Double, TrackDataLastLocationEntity.Heading);
                database.AddInParameter(commandWrapper, "@Speed", DbType.Double, TrackDataLastLocationEntity.Speed);

                Utility.ExecuteNonQuery(database, commandWrapper);
            }

            public static string GetImeiTrackDataLastLocationByUser(int companyID, string username, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_TrackDataLastLocation_GetGPSStockByUser", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@isUser", DbType.Boolean, isUser);
                database.AddInParameter(commandWrapper, "@isAdmin", DbType.Boolean, isAdmin);
                database.AddInParameter(commandWrapper, "@isMaster", DbType.Boolean, isMaster);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetImeiTrackDataLastLocationByCompanyIDAndBusRouteID(int companyID, int busRouteID)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_TrackDataLastLocation_GetGPSStockByCompanyIDAndBusRouteID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);

                try
                {
                    return Utility.ExecuteDataSet(database, commandWrapper).GetXml().ToString();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }


            public static DataSet GetGroupsTrackDataLastLocationByUser(int companyID, string username, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsLastLocation = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_TrackDataLastLocation_GetGPSGroupsByUser", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@isUser", DbType.Boolean, isUser);
                database.AddInParameter(commandWrapper, "@isAdmin", DbType.Boolean, isAdmin);
                database.AddInParameter(commandWrapper, "@isMaster", DbType.Boolean, isMaster);

                try
                {
                    dsLastLocation = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsLastLocation;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public static class GpsTypeProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GPSType_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
        }


        public static class GpsModelsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GPSModels_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
        }

        public static class GpsCommandsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GPSCommands_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
        }

        public static class MasterRolesProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.MasterRoles_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataSet GetByMasterUsername(string masterUsername)
            {
                DataSet dsMasterRoles = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MastersRolesByMasterUsername", true);

                database.AddInParameter(commandWrapper, "@MasterUsername", DbType.String, masterUsername);

                try
                {
                    dsMasterRoles = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMasterRoles;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }


        public static class AdministratorsRolesProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.AdministratorsRoles_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataTable GetAllToAssociateCompanies_Find(int companyID, string name)
            {
                DataSet dsRoles = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_AdministratorsRoles_GetAllToAssociateCompanies_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    dsRoles = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsRoles.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetAllByAdminUsernameAndCompanyID(int companyID, string adminUsername)
            {
                DataSet dsRoles = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_AdministratorsRoles_GetAllByAdminUsernameAndCompanies", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);

                try
                {
                    dsRoles = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsRoles.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class AdministratorsProvider
        {
            public static DataSet GetAdministratorsByCompanyIDByDepartmentID(int companyID, int departmentID)
            {

                DataSet Administrators = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Administrators_GetAdministratorsByCompanyIDByDepartmentID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@DepartmentID", DbType.String, departmentID);

                try
                {
                    Administrators = Utility.ExecuteDataSet(database, commandWrapper);
                    return Administrators;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyID(int companyID)
            {

                DataSet Administrators = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Administrators_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    Administrators = Utility.ExecuteDataSet(database, commandWrapper);
                    return Administrators;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyIDToAssociateDepartmentsFind(int companyID, int departmentID, string adminUsername, string name, string firstName, string lastName)
            {

                DataSet Administrators = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Administrators_GetByCompanyIDToAssociateDepartments_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@DepartmentID", DbType.Int64, departmentID);
                if (!string.IsNullOrEmpty(adminUsername)) database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(firstName)) database.AddInParameter(commandWrapper, "@FirstName", DbType.String, firstName);
                if (!string.IsNullOrEmpty(lastName)) database.AddInParameter(commandWrapper, "@LastName", DbType.String, lastName);

                try
                {
                    Administrators = Utility.ExecuteDataSet(database, commandWrapper);
                    return Administrators;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class AdministratorsByCompaniesProvider
        {
            public static DataSet GetByCompanyID(int companyId)
            {
                DataSet dsAdmin = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_AdministratorsByCompanies_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    dsAdmin = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsAdmin;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class UserRolesProvider
        {
            public static int GetNextID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.UserRoles_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataSet GetAllByCompanyID(int companyID)
            {
                DataSet dsCompanies = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_UserRoles_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsCompanies = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsCompanies;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetAllByUsernameAndCompanyID(int companyID, string username)
            {
                DataSet dsRoles = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_UserRoles_GetAllByUsernameAndCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    dsRoles = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsRoles.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public static class CompaniesProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Companies_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByAdminUsername(int companyID, string adminUsername, bool isMaster)
            {
                DataSet dsCompanies = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Companies_GetAllByAdminUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);
                database.AddInParameter(commandWrapper, "@isMaster", DbType.String, isMaster);

                try
                {
                    dsCompanies = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsCompanies.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            //Used by Map
            public static string GetInfoByUser(int companyID, string username)
            {
                DataSet dsCompanies = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Company_GetInfoByUser", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    dsCompanies = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsCompanies.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetInfoByCompanyID(int companyID)
            {
                DataSet dsCompanies = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Company_GetInfoByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsCompanies = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsCompanies.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetAllToAssociateMasterUsernameFind(int companyID, string masterUsername, string name)
            {

                DataSet Administrators = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Companies_GetAllToAssociateMasterUsername_Find", true);

                if (companyID != -1) database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                if (!string.IsNullOrEmpty(masterUsername)) database.AddInParameter(commandWrapper, "@MasterUsername", DbType.String, masterUsername);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    Administrators = Utility.ExecuteDataSet(database, commandWrapper);
                    return Administrators;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        } 

        public static class NotificationsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Notifications_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAll_TypeGeoFences() {
                DataSet dsNotification = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Notifications_GetAll_TypeGeoFences", true);

                try
                {
                    dsNotification = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsNotification.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool Insert_Delete_Update_TypeGeoFences(int geoFenceID, bool notyGeoFenceToSendAdmin, bool isUpdateNotyGeoFenceToSendAdmin, bool notyGPSImeiToSendUser, bool isUpdatGPSImeiToSendUser, int notificationID, StringCollection gpsImeiToInsert, StringCollection gpsImeiToDelete, string username, int companyID)
            {
                int sendToUser = 0;
                string gpsImei = "";

                SqlDatabase database;
                DbCommand commandWrapper;

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();

                    if (isUpdateNotyGeoFenceToSendAdmin)
                    {
                        database = new SqlDatabase(_connectionString);
                        commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Notifications_UpdateGeoFence_TypeGeoFences", true);

                        database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                        database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                        database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64, notificationID);
                        database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);
                        database.AddInParameter(commandWrapper, "@SendToAdmin", DbType.Byte, notyGeoFenceToSendAdmin);

                        database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);

                        foreach (string gpsImeiItem in gpsImeiToInsert)
                        {
                            database.SetParameterValue(commandWrapper, "@GPSImei", gpsImeiItem);
                            Utility.ExecuteScalar(tm, commandWrapper);
                        }
                    }

                    if (isUpdatGPSImeiToSendUser) {
                        database = new SqlDatabase(_connectionString);
                        commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Notifications_UpdateGPSImei_TypeGeoFences", true);

                        database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                        database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                        database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64, notificationID);
                        database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);
                        database.AddInParameter(commandWrapper, "@SendToUser", DbType.Byte, notyGPSImeiToSendUser);

                        database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);

                        foreach (string gpsImeiItem in gpsImeiToInsert)
                        {
                            database.SetParameterValue(commandWrapper, "@GPSImei", gpsImeiItem);
                            Utility.ExecuteScalar(tm, commandWrapper);
                        }
                    }
                    

                    database = new SqlDatabase(_connectionString);
                    commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Notifications_Insert_TypeGeoFences", true);

                    database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                    database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                    database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64, notificationID);
                    database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);
                    database.AddInParameter(commandWrapper, "@SendToAdmin", DbType.Byte, notyGeoFenceToSendAdmin);

                    database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);
                    database.AddInParameter(commandWrapper, "@SendToUser", DbType.Byte);

                    foreach (string gpsImeiItem in gpsImeiToInsert)
                    {
                        database.SetParameterValue(commandWrapper, "@GPSImei", gpsImeiItem);
                        database.SetParameterValue(commandWrapper, "@SendToUser", notyGPSImeiToSendUser);

                        Utility.ExecuteScalar(tm, commandWrapper);                    
                    }

                    database = new SqlDatabase(_connectionString);
                    commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Notifications_Delete_TypeGeoFences", true);

                    database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                    database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                    database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64, notificationID);
                    database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);

                    database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);

                    foreach (string gpsImeiItem in gpsImeiToDelete)
                    {
                        database.SetParameterValue(commandWrapper, "@GPSImei", gpsImeiItem);
                        Utility.ExecuteScalar(tm, commandWrapper);
                    }

                    tm.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo asociar las notificaciones: " + e.Message);
                }
            }
        }

        public static class NotificationTrackDataProvider
        {
            public static DataSet GetAllByUserToday(int companyID, string username, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsNotificationTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_NotificationsTrackData_GetAllByUserToday", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.String, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@IsUser", DbType.Boolean, isUser);
                database.AddInParameter(commandWrapper, "@IsAdmin", DbType.Boolean, isAdmin);
                database.AddInParameter(commandWrapper, "@IsMaster", DbType.Boolean, isMaster);

                try
                {
                    dsNotificationTrackData = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsNotificationTrackData;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetAllByGPSImeiAndDate(string gpsImei, DateTime beginDate, DateTime endDate)
            {
                DataSet dsNotificationTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_NotificationsTrackData_GetAllByGPSImeiAndDate", true);

                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime, beginDate);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime, endDate);

                try
                {
                    dsNotificationTrackData = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsNotificationTrackData.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class DepartmentsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Departments_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByCompanyIDAndAdminUsername(int companyID, string adminUsername) 
            { 
                DataSet dsDepartments = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Departments_GetAllByCompanyIDAndAdminUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);

                try
                {
                    dsDepartments = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsDepartments.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByAdminUsernameByCompanyID(string adminUsername, int companyID)
            {

                DataSet Departments = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Departments_GetByAdminUsernameByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);

                try
                {
                    Departments = Utility.ExecuteDataSet(database, commandWrapper);
                    return Departments;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public static class GroupsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Groups_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
        }

        public static class ClientsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Clients_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataSet GetAllByCompanyIDAndClientGroupID(int companyID, int clientGroupID){
                DataSet dsClients = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_GetAllByCompanyIDAndClientGroupID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@ClientGroupID", DbType.Int64, clientGroupID);

                try
                {
                    dsClients = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClients;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            
            public static string GetAllByCompanyID(int companyID)
            {
                DataSet dsClients = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsClients = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClients.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int Insert(int companyID, int clientGroupID, string name, float? latitude, float? longitude, string phone1, string phone2, string typeUserTransaction, string usernameTransaction)
            {
                int clientID = -1;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_Insert", true);

                DateTime date = DateTime.Now;                

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@ClientGroupID", DbType.Int64, clientGroupID);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (latitude != null)
                {
                    database.AddInParameter(commandWrapper, "@Latitude", DbType.Decimal, latitude);
                }
                if (longitude != null)
                {
                    database.AddInParameter(commandWrapper, "@Longitude", DbType.Decimal, longitude);
                }
                database.AddInParameter(commandWrapper, "@Phone1", DbType.String, phone1);
                database.AddInParameter(commandWrapper, "@Phone2", DbType.String, phone2);                
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, date);
                database.AddInParameter(commandWrapper, "@LastTick", DbType.Int64, TickManager.TickManager.GetTick(date));
                database.AddInParameter(commandWrapper, "@TypeUserTransaction", DbType.String, typeUserTransaction);
                database.AddInParameter(commandWrapper, "@UsernameTransaction", DbType.String, usernameTransaction);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();                

                try
                {
                    tm.BeginTransaction();
                    clientID = (int) Utility.ExecuteScalar(tm, commandWrapper);
                    tm.Commit();
                    return clientID;
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo insertar el cliente: " + e.Message);
                }
            }

            public static bool Delete(int clientGroupID, int clientID)
            { 
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_Delete", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@ClientGroupID", DbType.Int64, clientGroupID);
                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientID);
                
                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();                

                try
                {
                    tm.BeginTransaction();
                    rowDeleted = (int) Utility.ExecuteNonQuery(tm, commandWrapper);
                    tm.Commit();
                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo eliminar el cliente: " + e.Message);
                }
            }

            public static bool UpdateIfIsNewerSync(GPSTrackingDBV2.Data.TransactionManager tm, int clientId, string name, string phone1, string phone2, float latitude, float longitude, Int64 lastTick)
            {
                int rowUpdate;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_UpdateIfIsNewerSync", true);

                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Phone1", DbType.String, phone1);
                database.AddInParameter(commandWrapper, "@Phone2", DbType.String, phone2);
                database.AddInParameter(commandWrapper, "@Latitude", DbType.Decimal, latitude);
                database.AddInParameter(commandWrapper, "@Longitude", DbType.Decimal, longitude);
                database.AddInParameter(commandWrapper, "@LastTick", DbType.Int64, lastTick);

                try
                {

                    rowUpdate = (int)Utility.ExecuteNonQuery(tm, commandWrapper);

                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el cliente: " + e.Message);
                }
            }

            public static bool DeleteSync(GPSTrackingDBV2.Data.TransactionManager tm, int clientGroupID, int clientID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Clients_DeleteSync", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@ClientGroupID", DbType.Int64, clientGroupID);
                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientID);

                try
                {
                    rowDeleted = (int)Utility.ExecuteNonQuery(tm, commandWrapper);

                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo eliminar el cliente: " + e.Message);
                }
            }
        }

        public static class ClientsLogByUuidProvider{
            
            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int clientId, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@ClientId", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el ClientLogByUuid: " + e.Message);
                }
            }
        }

        public static class UsersLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_UsersLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int companyId, string username, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_UsersLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el UsersLogByUuid: " + e.Message);
                }
            }
        }

        public static class StockLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId, int? totalToGet)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                if(totalToGet.HasValue)
                    database.AddInParameter(commandWrapper, "@TotalToGet", DbType.Int64, totalToGet);


                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int GetTotalToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockLogByUuid_GetTotalToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                
                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return int.Parse(ds.Tables[0].Rows[0]["Total"].ToString());
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int companyId, int stockId, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@StockID", DbType.Int64, stockId);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el StockByUuid: " + e.Message);
                }
            }
        }

        public static class StockFilesLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int companyId, int stockFileId, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@StockFileID", DbType.Int64, stockFileId);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el StockFilesLogByUuid: " + e.Message);
                }
            }
        }

        public static class StockFilesTypeOfFileLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesTypeOfFileLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int stockFileTypeOfFileId, string uuid, Int64 tick, DateTime transactionDate)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesTypeOfFileLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@StockFileTypeOfFileID", DbType.Int64, stockFileTypeOfFileId);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, transactionDate);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el StockFilesLogByUuid: " + e.Message);
                }
            }
        }

        public static class StockFilesByUsernameLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesByUsernameLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int companyId, int stockFileId, string username, string uuid, Int64 tick, DateTime transactionDate)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesByUsernameLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@StockFileID", DbType.Int64, stockFileId);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, transactionDate);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el StockFilesByUsernameLogByUuid: " + e.Message);
                }
            }
        }

        public static class BusStopProvider
        {
            
            public static int Insert(int companyID, int busRouteID, string name, float latitude, float longitude, bool isTerminal, bool isHalfTerminal)
            {
                int busStopID = -1;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusStop_Insert", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Latitude", DbType.Decimal, latitude);
                database.AddInParameter(commandWrapper, "@Longitude", DbType.Decimal, longitude);
                database.AddInParameter(commandWrapper, "@IsTerminal", DbType.Boolean, isTerminal);
                database.AddInParameter(commandWrapper, "@IsHalfTerminal", DbType.Boolean, isHalfTerminal);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    busStopID = (int)Utility.ExecuteScalar(tm, commandWrapper);
                    tm.Commit();
                    return busStopID;
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo insertar la parada de Bus: " + e.Message);
                }
            }

            public static bool Delete(int busRouteID, int busStopID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusStop_Delete", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);
                database.AddInParameter(commandWrapper, "@BusStopID", DbType.Int64, busStopID);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    rowDeleted = (int)Utility.ExecuteNonQuery(tm, commandWrapper);
                    tm.Commit();
                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo eliminar la parada de bus: " + e.Message);
                }
            }

            public static string GetAllByCompanyIDAndBusRouteID(int companyID, int busRouteID)
            {
                DataSet dsBusStops = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusStop_GetAllByCompanyIDAndBusRouteID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);

                try
                {
                    dsBusStops = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsBusStops.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public static class BusRoutesProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.BusRoutes_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByCompanyID(int companyID)
            {
                DataSet dsBusRoutes = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutes_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsBusRoutes = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsBusRoutes.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int Insert(int companyID, string name)
            {
                int busRouteID = -1;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutes_Insert", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    busRouteID = (int)Utility.ExecuteScalar(tm, commandWrapper);
                    tm.Commit();
                    return busRouteID;
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo insertar la ruta de Bus: " + e.Message);
                }
            }

            public static bool Delete(int companyID, int busRouteID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutes_Delete", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    rowDeleted = (int)Utility.ExecuteNonQuery(tm, commandWrapper);
                    tm.Commit();
                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo eliminar la ruta de bus: " + e.Message);
                }
            }

            public static DataSet GetByCompanyIDToAssociateVPCControlFind(int companyID, int busRouteID, string name)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_BusRoutes_GetByCompanyIDToAsocciateVPCControl", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                if (busRouteID != -1) database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class BusRoutesDetailsProvider
        {
            public static void Insert(int busRouteID, int pos, float latitude, float longitude)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutesDetails_Insert", true);

                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);
                database.AddInParameter(commandWrapper, "@Pos", DbType.Int64, pos);
                database.AddInParameter(commandWrapper, "@Latitude", DbType.Decimal, latitude);
                database.AddInParameter(commandWrapper, "@Longitude", DbType.Decimal, longitude);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    Utility.ExecuteScalar(tm, commandWrapper);
                    tm.Commit();
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo insertar el detalle de la ruta de Bus: " + e.Message);
                }
            }

            public static bool DeleteAllByBusRouteID(int busRouteID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutesDetails_DeleteAllByBusRouteID", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    rowDeleted = (int)Utility.ExecuteNonQuery(tm, commandWrapper);
                    tm.Commit();
                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo eliminar el detalle de la ruta de bus: " + e.Message);
                }
            }

            public static string GetAllByBusRouteID(int busRouteID)
            {
                DataSet dsBusRoutesDetails = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_BusRoutesDetails_GetAllByBusRouteID", true);

                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64, busRouteID);

                try
                {
                    dsBusRoutesDetails = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsBusRoutesDetails.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class ClientsGroupsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.ClientsGroups_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByCompanyID(int companyID)
            {
                DataSet dsClientsGroups = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_ClientsGroups_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsClientsGroups = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClientsGroups.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class TrackDataProvider
        {

            public static StringCollection GetAllByImeiAndDate(string imei, DateTime date, bool isFirstSelection)
            {

                DataSet dsTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                StringCollection imeiInfoList = new StringCollection();

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_TrackData_GetAllByImeiAndDate", true);
                database.AddInParameter(commandWrapper, "@Imei", DbType.String, imei);
                database.AddInParameter(commandWrapper, "@DateTime", DbType.DateTime, date);
                database.AddInParameter(commandWrapper, "@isFirstSelection", DbType.Boolean, isFirstSelection);

                try
                {
                    dsTrackData = Utility.ExecuteDataSet(database, commandWrapper);

                    for (int i = 0; i < dsTrackData.Tables[0].Rows.Count; i++)
                    {
                        if (imeiInfoList.Count == 0)
                        {
                            imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["GPSImei"].ToString() + "&" +
                                                dsTrackData.Tables[0].Rows[i]["Image"].ToString());
                        }

                        imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["Hour"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Speed"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Latitude"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Longitude"].ToString());
                                            //dsTrackData.Tables[0].Rows[i]["PeopleIn"].ToString() + "&" +
                                            //dsTrackData.Tables[0].Rows[i]["PeopleOut"].ToString() + "&" +
                                            //dsTrackData.Tables[0].Rows[i]["PeopleTotalInside"].ToString() + "&" +
                                            //dsTrackData.Tables[0].Rows[i]["PeopleTotalIn"].ToString() + "&" +
                                            //dsTrackData.Tables[0].Rows[i]["PeopleTotalOut"].ToString());
                    }

                    return imeiInfoList;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class GPSStockProvider
        {
            public static DataSet GetAll() {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetAll", true);
    
                try
                {
                    dsGPSStock = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSStock;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetAllByGPSGroupIDAndNotificationIDAndGeoFenceID(int gpsGroupID,  int notificationID, int geoFenceID, string username, int companyID)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetAllByGPSGroupIDAndNotificationIDAndGeoFenceID", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@GPSGroupID", DbType.Int64, gpsGroupID);
                database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64, notificationID);
                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);

                try
                {
                    dsGPSStock = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSStock.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            
            public static string GetAllByUsername(int companyID, string username)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetAllByUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    dsGPSStock = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSStock.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetAllByUsernameAndGPSGroupID(int companyID, string username, int gpsgroupId)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetAllByUsernameAndGPSGroupID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSGroupID", DbType.Int64, gpsgroupId);

                try
                {
                    dsGPSStock = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSStock;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetGPSGroupsByUsername(int companyID, string username, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsGPSStock = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetGPSGroupsbyUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);                 
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@isUser", DbType.Boolean, isUser);
                database.AddInParameter(commandWrapper, "@isAdmin", DbType.Boolean, isAdmin);
                database.AddInParameter(commandWrapper, "@isMaster", DbType.Boolean, isMaster);                 

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);                    
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetAdminUsernameByGPSImei(string imei)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GPSStock_Get_AdminUsernameByGPSImei", true);
                
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, imei);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);

                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetUsernameByGPSImei(string imei)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GPSStock_Get_UsernameByGPSImei", true);
                
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, imei);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);

                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetGPSStockToAssociateGPSGroupsFind(string gpsImei, string name, string simNumber, string description, int gpsTypeID, int gpsModelID) {
                
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_ToAssociateGPSGroup_Find", true);

                if (!string.IsNullOrEmpty(gpsImei)) database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(simNumber)) database.AddInParameter(commandWrapper, "@SimNumber", DbType.String, simNumber);
                if (!string.IsNullOrEmpty(description)) database.AddInParameter(commandWrapper, "@Description", DbType.String, description);
                if (gpsTypeID != -1) database.AddInParameter(commandWrapper, "@GPSTypeID", DbType.Int64, gpsTypeID);
                if (gpsModelID != -1) database.AddInParameter(commandWrapper, "@GPSModelID", DbType.Int64, gpsModelID);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetGPSStockToAssociateUsersByGPSGroupID_Find(int companyID, string username, int gpsGroupId, string gpsImei, string name, string simNumber, string description, int gpsTypeID, int gpsModelID)
            {

                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_ToAssociateUsersByGPSGroupID_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSGroupID", DbType.Int64, gpsGroupId);
                if (!string.IsNullOrEmpty(gpsImei)) database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(simNumber)) database.AddInParameter(commandWrapper, "@SimNumber", DbType.String, simNumber);
                if (!string.IsNullOrEmpty(description)) database.AddInParameter(commandWrapper, "@Description", DbType.String, description);
                if (gpsTypeID != -1) database.AddInParameter(commandWrapper, "@GPSTypeID", DbType.Int64, gpsTypeID);
                if (gpsModelID != -1) database.AddInParameter(commandWrapper, "@GPSModelID", DbType.Int64, gpsModelID);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyIDToAssociateVPCControlFind(int companyID, string gpsImei, string name, string simNumber, string description)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetByCompanyIDToAssociateVPCControl_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                if (!string.IsNullOrEmpty(gpsImei)) database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(simNumber)) database.AddInParameter(commandWrapper, "@SimNumber", DbType.String, simNumber);
                if (!string.IsNullOrEmpty(description)) database.AddInParameter(commandWrapper, "@Description", DbType.String, description);

                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyIdAndGPSImei(int companyID, string gpsImei)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSStock_GetByCompanyIdAndGPSImei", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                
                try
                {
                    DataSet ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        
        public class GeoFencesProvider
        {
            public static string GetAllByCompanyID(int companyID)
            {
                DataSet dsGeoFences = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GeoFences_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsGeoFences = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGeoFences.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string GetByGeoFenceID(int geoFenceID)
            {
                DataSet dsGeoFences = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GeoFences_GetByGeoFenceID", true);

                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);

                try
                {
                    dsGeoFences = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGeoFences.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int Insert(int companyID, string name) {
                int geoFenceID = -1;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GeoFences_Insert", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    geoFenceID = (int)Utility.ExecuteScalar(database, commandWrapper);
                    return geoFenceID;
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo insertar la GeoFence: " + e.Message);
                }   
            }

            public static bool Delete(int companyID, int geoFenceID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GeoFences_Delete", true);
                int rowDeleted;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    rowDeleted = (int)Utility.ExecuteNonQuery(tm, commandWrapper);
                    tm.Commit();
                    if (rowDeleted > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo eliminar la GeoFence: " + e.Message);
                }
            }

            public static bool SetPolygonByGeoFenceID(int geoFenceID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_GeoFences_SetPolygonByGeoFenceID", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64, geoFenceID);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el Polygono de la GeoFence: " + e.Message);
                }
            }
        }

        public class GPSGroupsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.GPSGroups_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByCompanyIDAndAdminUsername(int companyID, string username)
            {
                DataSet dsGPSGroups = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSGroups_GetAllByCompanyIDAndAdminUsername", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsGPSGroups = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSGroups.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetGPSGroupsToAssociateBusRouteFind(int companyID, string name)
            {
                DataSet dsGPSGroups = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_GPSGroups_ToAssociateBusRoute_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    dsGPSGroups = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGPSGroups;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class ReportsProvider
        {
            public static DataSet ResumenClientsByDateByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsResumen = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Reports_ResumenClientsByDateByCompanyID", true);

                commandWrapper.CommandTimeout = 0;

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime, dateEnd);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsResumen = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsResumen;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet ResumenBusStopsPeopleInOutByDateByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID, bool reprocess)
            {
                DataSet dsResumen = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Reports_ResumenBusStopsPeopleInOutByDateByCompanyID", true);

                commandWrapper.CommandTimeout = 0;

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime, dateEnd);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Reprocess", DbType.Boolean, reprocess);

                try
                {
                    dsResumen = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsResumen;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet ResumenBusRoutePeopleInOutByDateByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID, bool reprocess)
            {
                DataSet dsResumen = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Reports_ResumenBusRoutePeopleInOutByDateByCompanyID", true);

                commandWrapper.CommandTimeout = 0;

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime, dateEnd);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Reprocess", DbType.Boolean, reprocess);

                try
                {
                    dsResumen = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsResumen;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet DetailsBusStopsPeopleInOutByDateByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsResumen = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Reports_DetailsBusStopsPeopleInOutByDateByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime, dateEnd);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsResumen = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsResumen;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class UsersGroupsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.UsersGroups_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static string GetAllByDepartmentID(int departmentID)
            {
                DataSet dsGroupsProvider = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_UsersGroups_GetAllByDepartmentID", true);

                database.AddInParameter(commandWrapper, "@DepartmentID", DbType.Int64, departmentID);

                try
                {
                    dsGroupsProvider = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsGroupsProvider.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class DriverProvider
        {

            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Drivers_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataSet GetByCompanyIDToAssociateVPCControlFind(int companyID, int driverID, string driverName, string identification, string name, string firstSurname, string secondSurname, string phone)
            {
                DataSet dsDriver = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Drivers_GetByCompanyIDToAssociateVPCControl_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                if (driverID != -1) database.AddInParameter(commandWrapper, "@DriverID", DbType.Int64, driverID);
                if (!string.IsNullOrEmpty(driverName)) database.AddInParameter(commandWrapper, "@DriverName", DbType.String, driverName);
                if (!string.IsNullOrEmpty(identification)) database.AddInParameter(commandWrapper, "@Identification", DbType.String, identification);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(firstSurname)) database.AddInParameter(commandWrapper, "@FirstSurname", DbType.String, firstSurname);
                if (!string.IsNullOrEmpty(secondSurname)) database.AddInParameter(commandWrapper, "@SecondSurname", DbType.String, secondSurname);
                if (!string.IsNullOrEmpty(phone)) database.AddInParameter(commandWrapper, "@Phone", DbType.String, phone);

                try
                {
                    dsDriver = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsDriver;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyIDByDriverID(int companyID, int driverID)
            {
                DataSet dsDriver = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Drivers_GetByCompanyIDToAssociateVPCControl_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@DriverID", DbType.Int64, driverID);
                
                try
                {
                    dsDriver = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsDriver;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class VPCProcessDetailsProvider {
            public static DataSet GetBusRoutesByDatesByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCProcessDetails_GetBusRoutesByDatesByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin.Date);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd.Date);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            public static DataSet GetBusStopsByDatesByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCProcessDetails_GetBusStopsByDatesByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin.Date);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd.Date);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            public static StringCollection GetAllByNumControl(int numControl, bool isFirstSelection)
            {
                DataSet dsTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                StringCollection imeiInfoList = new StringCollection();

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_VPCProcessDetails_GetAllByNumControl", true);
                
                database.AddInParameter(commandWrapper, "@NumControl", DbType.Int64, numControl);
                database.AddInParameter(commandWrapper, "@IsFirstSelection", DbType.Boolean, isFirstSelection);

                try
                {
                    dsTrackData = Utility.ExecuteDataSet(database, commandWrapper);

                    for (int i = 0; i < dsTrackData.Tables[0].Rows.Count; i++)
                    {
                        if (imeiInfoList.Count == 0)
                        {
                            imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["GPSImei"].ToString() + "&" +
                                                dsTrackData.Tables[0].Rows[i]["Image"].ToString());
                        }

                        imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["Hour"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Speed"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Latitude"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Longitude"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["PeopleIn"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["PeopleOut"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["PeopleTotalInside"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["PeopleTotalIn"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["PeopleTotalOut"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["TotalTrack"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["TotalKm"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["DistanceKm"].ToString());
                    }

                    return imeiInfoList;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            public static DataSet GetResumenRouteHistory(int numControl)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCProcessDetails_GetResumenRouteHistory", true);

                database.AddInParameter(commandWrapper, "@NumControl", DbType.Int64, numControl);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class NotificationsControlProvider
        {
            public static DataSet GetByDatesByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_NotificationsControl_GetByDatesByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin.Date);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd.Date);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static void ExecuteProcessByCompanyID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_NotificationsGeoFences_ForJob", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Threats", DbType.Int64, 3);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo ejecutar el proceso, Error: " + e.Message);
                }
            }

            public static string ClearByPrimaryKey(int companyID, System.Collections.Generic.List<string> username, System.Collections.Generic.List<string> GPSImeis, System.Collections.Generic.List<string> dates, System.Collections.Generic.List<string> geoFences, System.Collections.Generic.List<string> notifications)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_NotificationsControl_ClearByPrimaryKey", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date);
                database.AddInParameter(commandWrapper, "@GeoFenceID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@NotificationID", DbType.Int64);

                for (int i = 0; i < username.Count; i++)
                {
                    try
                    {
                        database.SetParameterValue(commandWrapper, "@Username", username[i]);
                        database.SetParameterValue(commandWrapper, "@CompanyID", companyID);
                        database.SetParameterValue(commandWrapper, "@GPSImei", GPSImeis[i]);
                        database.SetParameterValue(commandWrapper, "@Date", DateTime.Parse(dates[i]).Date);
                        database.SetParameterValue(commandWrapper, "@GeoFenceID", int.Parse(geoFences[i].ToString()));
                        database.SetParameterValue(commandWrapper, "@NotificationID", int.Parse(notifications[i].ToString()));

                        Utility.ExecuteScalar(database, commandWrapper);

                    }
                    catch (Exception e)
                    {
                        if (e.Message.Contains("No se puede limpiar el registro porque el mismo se encuentra en proceso de ejecución, intente una vez finalizado el proceso."))
                        {
                            if (string.IsNullOrEmpty(result))
                            {
                                result += username[i] + " - " + GPSImeis[i] + " - " + dates[i] + " - " + notifications[i] + " - " + geoFences[i];
                            }
                            else
                            {
                                result += "," + username[i] + " - " + GPSImeis[i] + " - " + dates[i] + notifications[i] + " - " + geoFences[i];
                            }
                        }
                        else
                        {
                            throw new Exception(e.Message);
                        }
                    }
                }

                return result;
            }
        }

        public class ClientsControlProvider
        {
            public static StringCollection GetAllTrackDataByPrimaryKey(int companyID, string username, string gpsImei, DateTime date, bool isFirstSelection)
            {
                DataSet dsTrackData = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);

                StringCollection imeiInfoList = new StringCollection();

                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_ClientsControl_GetAllTrackDataByPrimaryKey", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);
                database.AddInParameter(commandWrapper, "@IsFirstSelection", DbType.Boolean, isFirstSelection);

                try
                {
                    dsTrackData = Utility.ExecuteDataSet(database, commandWrapper);

                    for (int i = 0; i < dsTrackData.Tables[0].Rows.Count; i++)
                    {
                        if (imeiInfoList.Count == 0)
                        {
                            imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["GPSImei"].ToString() + "&" +
                                                dsTrackData.Tables[0].Rows[i]["Image"].ToString());
                        }

                        imeiInfoList.Add(dsTrackData.Tables[0].Rows[i]["Hour"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Speed"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Latitude"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["Longitude"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["TotalKm"].ToString() + "&" +
                                            dsTrackData.Tables[0].Rows[i]["DistanceKm"].ToString());
                    }

                    return imeiInfoList;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByDatesByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsControl_GetByDatesByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin.Date);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd.Date);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static void ExecuteProcessByCompanyID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_TrackDataByClients_ForJob", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Threats", DbType.Int64, 3);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo ejecutar el proceso, Error: " + e.Message);
                }
            }

            public static string ClearByPrimaryKey(int companyID, System.Collections.Generic.List<string> username, System.Collections.Generic.List<string> GPSImeis, System.Collections.Generic.List<string> dates)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsControl_ClearByPrimaryKey", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date);

                for (int i = 0; i < username.Count; i++)
                {
                    try
                    {
                        database.SetParameterValue(commandWrapper, "@Username", username[i]);
                        database.SetParameterValue(commandWrapper, "@CompanyID", companyID);
                        database.SetParameterValue(commandWrapper, "@GPSImei", GPSImeis[i]);
                        database.SetParameterValue(commandWrapper, "@Date", DateTime.Parse(dates[i]).Date);

                        Utility.ExecuteScalar(database, commandWrapper);

                    }
                    catch (Exception e)
                    {
                        if (e.Message.Contains("No se puede limpiar el registro porque el mismo se encuentra en proceso de ejecución, intente una vez finalizado el proceso."))
                        {
                            if (string.IsNullOrEmpty(result))
                            {
                                result += username[i] + " - " + GPSImeis[i] + " - " + dates[i];
                            }
                            else
                            {
                                result += "," + username[i] + " - " + GPSImeis[i] + " - " + dates[i];
                            }
                        }
                        else
                        {
                            throw new Exception(e.Message);
                        }
                    }
                }

                return result;
            }
        }

        public class VPCProcessProvider
        {
            public static void ExecuteProcessByCompanyID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_VPCProcess_ForJob", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Threats", DbType.Int64, 3);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo ejecutar el proceso, Error: " + e.Message);
                }
            }

            public static string ClearByNumControlByLine(System.Collections.Generic.List<string> numControl, System.Collections.Generic.List<string> line)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCProcess_ClearByNumControlByLine", true);

                database.AddInParameter(commandWrapper, "@NumControl", DbType.Int64);
                database.AddInParameter(commandWrapper, "@Line", DbType.Int64);

                for (int i = 0; i < numControl.Count; i++)
                {
                    try
                    {

                        database.SetParameterValue(commandWrapper, "@NumControl", numControl[i]);
                        database.SetParameterValue(commandWrapper, "@Line", line[i]);
                        Utility.ExecuteScalar(database, commandWrapper);

                    }
                    catch (Exception e)
                    {
                        if (e.Message.Contains("No se puede limpiar el registro porque el mismo se encuentra en proceso de ejecución, intente una vez finalizado el proceso.")){
                            if(string.IsNullOrEmpty(result)){
                                result += numControl[i];
                            }
                            else{
                                result += "," + numControl[i];
                            }
                        }
                        else{
                            throw new Exception(e.Message);
                        }
                    }
                }

                return result;
            }

            public static DataSet GetByDatesByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsProcess = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCProcess_GetByDatesByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin.Date);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd.Date);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsProcess = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsProcess;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class CompaniesByMenuProvider
        {
            public static DataTable GetAllByCompanyID(int companyID)
            {
                DataSet dsMenus = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_CompaniesByMenu_GetAllByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsMenus = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenus.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class VPCControlProvider
        {
            public static void DeleteByNumControl(int numControl)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCControl_DeleteByNumControl", true);

                database.AddInParameter(commandWrapper, "@NumControl", DbType.Int64, numControl);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }

            }

            public static DataSet GetByDateByCompanyID(DateTime dateBegin, DateTime dateEnd, int companyID)
            {
                DataSet dsControl = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCControl_GetByDateByCompanyID", true);

                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                try
                {
                    dsControl = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsControl;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static string Insert(int companyID, System.Collections.Generic.List<DateTime> dates, int driverID, string gpsImei, int busRouteID, System.Collections.Generic.List<DateTime> datesBegin, System.Collections.Generic.List<DateTime> datesEnd)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_VPCControl_Insert", true);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                string result = string.Empty;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date);
                database.AddInParameter(commandWrapper, "@DriverID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String);
                database.AddInParameter(commandWrapper, "@BusRouteID", DbType.Int64);
                database.AddInParameter(commandWrapper, "@DateBegin", DbType.DateTime);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.DateTime);

                for (int i = 0; i < dates.Count; i++)
                {
                    try
                    {
                        tm.BeginTransaction();
                        database.SetParameterValue(commandWrapper, "@CompanyID", companyID);
                        database.SetParameterValue(commandWrapper, "@Date", dates[i].Date);
                        database.SetParameterValue(commandWrapper, "@DriverID", driverID);
                        database.SetParameterValue(commandWrapper, "@GPSImei", gpsImei);
                        database.SetParameterValue(commandWrapper, "@BusRouteID", busRouteID);
                        database.SetParameterValue(commandWrapper, "@DateBegin", datesBegin[i]);
                        database.SetParameterValue(commandWrapper, "@DateEnd", datesEnd[i]);

                        Utility.ExecuteScalar(tm, commandWrapper);
                        tm.Commit();
                    }
                    catch (Exception ex)
                    {
                        tm.Rollback();
                        if(string.IsNullOrEmpty(result))
                            result += String.Format("{0:dd/MM/yyyy}", dates[i].Date);
                        else
                            result += ", " + String.Format("{0:dd/MM/yyyy}", dates[i].Date);
                    }
                }
                return result;
            }
        }

        public class DriversProvider
        {
            public static DataSet GetByCompanyID(int companyId)
            {
                DataSet dsDrivers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Drivers_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    dsDrivers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsDrivers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class UsersProvider
        {
            //Used by Map
            public static string GetByAdminUsername(int companyID, string adminUsername)
            {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Users_GetByAdminUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers.GetXml();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByUserGroupID(int userGroupID)
            {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Users_GetByUserGroupID", true);

                database.AddInParameter(commandWrapper, "@UserGroupID", DbType.Int64, userGroupID);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyID(int companyId) {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Users_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            /*public static DataSet GetByCompanyID(int companyId)
            {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_UsersByCompanies_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }*/

            public static DataSet GetByCompanyIDByGPSGroupID(int companyId, int gpsGroupId)
            {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Users_GetByCompanyIDByGPSGroupID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@GPSGroupID", DbType.Int64, gpsGroupId);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByCompanyIDToAssociateGPSGroupsFind(int companyId, string username, string name, string firstName, string lastName)
            {
                DataSet dsUsers = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Users_GetByCompanyIDToAssociateGPSGroups_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                if (!string.IsNullOrEmpty(username)) database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (!string.IsNullOrEmpty(firstName)) database.AddInParameter(commandWrapper, "@FirstName", DbType.String, firstName);
                if (!string.IsNullOrEmpty(lastName)) database.AddInParameter(commandWrapper, "@LastName", DbType.String, lastName);

                try
                {
                    dsUsers = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsUsers;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class MastersByCompaniesProvider
        {
            public static DataSet GetByCompanyID(int companyId)
            {
                DataSet dsMaster = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MastersByCompanies_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    dsMaster = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMaster;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public static class MastersProvider
        {
            public static DataSet GetAll()
            {
                DataSet dsMaster = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Masters_GetAll", true);

                try
                {
                    dsMaster = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMaster;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class MenuItemsProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.MenuItems_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataTable GetByUsername(int companyID, string username)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuItems_GetByUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetByAdminUsername(int companyID, string adminUsername) {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuItems_GetByAdminUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetByMasterUsername(string masterUsername)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuItems_GetByMasterUsername", true);

                database.AddInParameter(commandWrapper, "@MasterUsername", DbType.String, masterUsername);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetByRoleID(int roleID, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuItems_GetByRoleID", true);

                database.AddInParameter(commandWrapper, "@RoleID", DbType.Int64, roleID);
                if (isUser) database.AddInParameter(commandWrapper, "@isUser", DbType.String, isUser);
                if (isAdmin) database.AddInParameter(commandWrapper, "@isAdmin", DbType.String, isAdmin);
                if (isMaster) database.AddInParameter(commandWrapper, "@isMaster", DbType.String, isMaster);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }   

            public static DataTable GetAllToAssociateRoles_Find(int roleId, string description, string value, string valueOwner, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuItems_GetAllToAssociateRoles_Find", true);

                database.AddInParameter(commandWrapper, "@RoleID", DbType.Int64, roleId);

                if (!string.IsNullOrEmpty(description)) database.AddInParameter(commandWrapper, "@Description", DbType.String, description);
                if (!string.IsNullOrEmpty(value)) database.AddInParameter(commandWrapper, "@Value", DbType.String, value);
                if (!string.IsNullOrEmpty(valueOwner)) database.AddInParameter(commandWrapper, "@ValueOwner", DbType.String, valueOwner);
                if (isUser) database.AddInParameter(commandWrapper, "@IsUser", DbType.Boolean, isUser);
                if (isAdmin) database.AddInParameter(commandWrapper, "@IsAdmin", DbType.Boolean, isAdmin);
                if (isMaster) database.AddInParameter(commandWrapper, "@IsMaster", DbType.Boolean, isMaster);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }


        public class OrderProvider
        {
            public static int GetNextID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Order_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static void updateStatus(int companyId,  int orderId, string statusId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Order_UpdateStatus", true);

                database.AddInParameter(commandWrapper, "@CompanyId", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@OrderId", DbType.Int64, orderId);
                database.AddInParameter(commandWrapper, "@StatusId", DbType.String, statusId);

                Utility.ExecuteNonQuery(database, commandWrapper);
            }

            

            public static DataTable GetAllByCompanyIDAndDates(int companyID, DateTime dateBegin, DateTime dateEnd)
            {
                DataSet dsOrders = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Order_GetAllByCompanyIDAndDates", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd);

                try
                {
                    dsOrders = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsOrders.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

        }

        public class OrderDetailProvider
        {
            public static DataTable GetAllByCompanyIDAndOrderID(int companyID, int orderID)
            {
                DataSet dsOrderDetail = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_OrderDetail_GetAllByCompanyIDAndOrderID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@OrderID", DbType.Int64, orderID);

                try
                {
                    dsOrderDetail = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsOrderDetail.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

        }

        public class StockProvider
        {
            public static int GetNextID(int companyID)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Stock_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
        }

        public class StockByAditionalImagesProvider
        {
            public static int GetNextID(int companyID, int stockId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.StockByAditionalImages_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@StockID", DbType.Int64, stockId);
                int NextID = 0;
                try
                {
                    NextID = (int)Utility.ExecuteScalar(database, commandWrapper);
                }
                catch (Exception ex) { 
                    //
                }

                return NextID;
            }
        }

        public static class StockByAditionalImagesLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId, int? totalToGet)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockByAditionalImagesLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                if (totalToGet.HasValue)
                    database.AddInParameter(commandWrapper, "@TotalToGet", DbType.Int64, totalToGet);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static int GetTotalToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockByAditionalImagesLogByUuid_GetTotalToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return int.Parse(ds.Tables[0].Rows[0]["Total"].ToString());
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int companyId, int stockId, int numImage, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockByAditionalImagesLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@StockID", DbType.Int64, stockId);
                database.AddInParameter(commandWrapper, "@NumImage", DbType.Int64, numImage);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el ClientLogByUuid: " + e.Message);
                }
            }
        }

        public static class ApplicationsProvider
        {

            public static bool UnlinkApp(string uuid, int companyId, int applicationId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Applications_UnlinkApp", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@ApplicationId", DbType.Int64, applicationId);

                try
                {
                    Utility.ExecuteNonQuery(database, commandWrapper);
                    return true;
                }
                catch (Exception e)
                {
                    throw new Exception("No se desenlazar la aplicación: " + e.Message);
                }
            }
        }

        public class MenuProvider
        {             
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_Menu_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataTable GetApplicationsMenu(int companyId, string username)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_GetApplicationsMenu", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                
                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static DataTable GetMenuParents(bool? isVisibleMaster, bool? isVisibleAdministrator, bool? isVisibleUser, int? menuTypeID)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_Get_Menu_Parents", true);

                if (isVisibleMaster.HasValue) database.AddInParameter(commandWrapper, "@IsVisibleMaster", DbType.Boolean, isVisibleMaster);
                if (isVisibleAdministrator.HasValue) database.AddInParameter(commandWrapper, "@IsVisibleAdministrator", DbType.Boolean, isVisibleAdministrator);
                if (isVisibleUser.HasValue) database.AddInParameter(commandWrapper, "@IsVisibleUser", DbType.Boolean, isVisibleUser);
                if (menuTypeID.HasValue) database.AddInParameter(commandWrapper, "@MenuTypeID", DbType.Int64, menuTypeID);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static DataTable GetMenuByParent(int parentID)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_Get_Menu_By_Parents", true);

                database.AddInParameter(commandWrapper, "@Parent", DbType.Int32, parentID);
                      

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static void DeleteParent(int parentID)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_Delete_Parent", true);

                database.AddInParameter(commandWrapper, "@ParentID", DbType.Int32, parentID);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }   
            }

            public static void DeleteChild(int menuID)
            {
                string result = string.Empty;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_Delete_By_MenuID", true);

                database.AddInParameter(commandWrapper, "@MenuID", DbType.Int32, menuID);

                try
                {
                    Utility.ExecuteScalar(database, commandWrapper);

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }   
            }

            public static DataTable GetMenuByMasterUsername(string masterUsername, int menuTypeId)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_GetMenuByMasterUsername", true);

                database.AddInParameter(commandWrapper, "@MasterUsername", DbType.String, masterUsername);
                database.AddInParameter(commandWrapper, "@MenuTypeId", DbType.Int32, menuTypeId);
                
                      
                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static DataTable GetMenuByAdminUsername(string adminUsername, int companyID, int menuTypeId)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_GetMenuByAdminUsername", true);

                database.AddInParameter(commandWrapper, "@AdminUsername", DbType.String, adminUsername);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyID);
                database.AddInParameter(commandWrapper, "@MenuTypeId", DbType.Int32, menuTypeId);
                      
                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static DataTable GetMenuByUsername(string username, int companyID, int menuTypeId)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_GetMenuByUsername", true);

                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyID);
                database.AddInParameter(commandWrapper, "@MenuTypeId", DbType.Int32, menuTypeId);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public static DataTable GetMenuByTypeSearch(int parent, bool? isVisibleMaster, bool? isVisibleAdministrator, bool? isVisibleUser, int roleId)
            {
                DataSet dsMenuItems = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Menu_GetMenuByTypeSearch", true);

                database.AddInParameter(commandWrapper, "@Parent", DbType.Int32, parent);
                database.AddInParameter(commandWrapper, "@IsVisibleMaster", DbType.Boolean, isVisibleMaster);
                database.AddInParameter(commandWrapper, "@IsVisibleAdministrator", DbType.Boolean, isVisibleAdministrator);
                database.AddInParameter(commandWrapper, "@IsVisibleUser", DbType.Boolean, isVisibleUser);
                database.AddInParameter(commandWrapper, "@RoleId", DbType.Int32, roleId);

                try
                {
                    dsMenuItems = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenuItems.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }

            }    
                
            
        }

        public static class EmailToSendStocksProvider
        {
            public static int GetNextID(int companyId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.EmailToSendStocks_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static bool UpdateSync(int companyId, int emailToSendStockId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_EmailToSendStocks_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@EmailToSendStockID", DbType.Int64, emailToSendStockId);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el EmailToSendStocks: " + e.Message);
                }
            }

            public static DataSet GetEmailPending(int companyId, string username)
            {                 
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_EmailToSendStocks_GetEmailPending", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyId);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
            
            }

        }

        public static class ClientsByEmailProvider
        {
            public static int GetNextID(int clientId)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.ClientsByEmail_Custom_Get_NextID", true);

                database.AddInParameter(commandWrapper, "@ClientId", DbType.Int64, clientId);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static int Exists(int clientId, string name, string email)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.ClientsByEmail_Exists", true);

                database.AddInParameter(commandWrapper, "@ClientId", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Email", DbType.String, email);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }
            
            public static bool UpdateIfIsNewerSync(GPSTrackingDBV2.Data.TransactionManager tm, int clientId, int emailId, string name, string email, Int64 lastTick)
            {
                int rowUpdate;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_ClientsByEmail_UpdateIfIsNewerSync", true);

                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@EmailID", DbType.Int64, emailId);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Email", DbType.String, email);
                database.AddInParameter(commandWrapper, "@LastTick", DbType.Int64, lastTick);

                try
                {

                    rowUpdate = (int)Utility.ExecuteNonQuery(tm, commandWrapper);

                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el cliente: " + e.Message);
                }
            }

            public static int Insert(int companyId, int clientId, string name, string email, string typeUserTransaction, string usernameTransaction)
            {
                int clientID = -1;
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Custom_ClientsByEmail_Insert", true);

                DateTime date = DateTime.Now;

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Email", DbType.String, email);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, date);
                database.AddInParameter(commandWrapper, "@LastTick", DbType.Int64, TickManager.TickManager.GetTick(date));
                database.AddInParameter(commandWrapper, "@TypeUserTransaction", DbType.String, typeUserTransaction);
                database.AddInParameter(commandWrapper, "@UsernameTransaction", DbType.String, usernameTransaction);

                GPSTrackingDBV2.Data.TransactionManager tm = GPSTrackingDBV2.Data.DataRepository.Provider.CreateTransaction();

                try
                {
                    tm.BeginTransaction();
                    clientID = (int)Utility.ExecuteScalar(tm, commandWrapper);
                    tm.Commit();
                    return clientID;
                }
                catch (Exception e)
                {
                    tm.Rollback();
                    throw new Exception("No se pudo insertar el correo del cliente: " + e.Message);
                }
            }
        }

        public static class ClientsByEmailLogByUuidProvider
        {
            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsByEmailLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int clientId, int emailId, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsByEmailLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientId);
                database.AddInParameter(commandWrapper, "@EmailID", DbType.Int64, emailId);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el ClientByEmailLogByUuid: " + e.Message);
                }
            }

        }

        public static class StockCategoriesLogByUuidProvider
        {

            public static DataTable GetToSync(string uuid, int companyId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockCategoriesLogByUuid_GetToSync", true);

                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static bool UpdateSync(int stockCategoryId, string uuid, Int64 tick, DateTime dateTransaction)
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockCategoriesLogByUuid_UpdateSync", true);
                int rowUpdate;

                database.AddInParameter(commandWrapper, "@StockCategoryId", DbType.Int64, stockCategoryId);
                database.AddInParameter(commandWrapper, "@Uuid", DbType.String, uuid);
                database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, dateTransaction);
                database.AddInParameter(commandWrapper, "@Tick", DbType.Int64, tick);

                try
                {
                    rowUpdate = (int)Utility.ExecuteNonQuery(database, commandWrapper);
                    if (rowUpdate > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw new Exception("No se pudo actualizar el ClientLogByUuid: " + e.Message);
                }
            }
        }

        public class StockCategoriesProvider
        {

            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "StockCategories_Custom_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }

            public static DataTable GetAllByCompanyID(int companyId, string name)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockCategories_GetByCompanyID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyId);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetByStockCategoryId(int stockCategoryId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockCategories_GetAllByStockCategoryID", true);

                database.AddInParameter(commandWrapper, "@StockCategoryId", DbType.Int64, stockCategoryId);

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataTable GetByParentStockCategoryId(int companyID, int? parentStockCategoryId)
            {
                DataSet ds = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockCategories_GetAllByParentStockCategoryID", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);

                if (parentStockCategoryId.HasValue)
                {
                    database.AddInParameter(commandWrapper, "@ParentStockCategoryId", DbType.Int64, parentStockCategoryId);
                }

                try
                {
                    ds = Utility.ExecuteDataSet(database, commandWrapper);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class MenuByMasterRoleProvider
        {
            public static DataTable GetAllByMasterRoleID(int masterRoleId)
            {
                DataSet dsMenus = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuByMasterRoles_GetAllByMasterRoleID", true);

                database.AddInParameter(commandWrapper, "@MasterRoleID", DbType.Int64, masterRoleId);

                try
                {
                    dsMenus = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenus.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }


        public class MenuByAdministratorRoleProvider
        {
            public static DataTable GetAllByAdministratorRoleID(int administratorRoleId, int companyId)
            {
                DataSet dsMenus = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuByAdministratorRoles_GetAllByAdministratorRoleID", true);

                database.AddInParameter(commandWrapper, "@AdministratorRoleID", DbType.Int32, administratorRoleId);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyId);

                try
                {
                    dsMenus = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenus.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class MenuByUserRoleProvider
        {
            public static DataTable GetAllByUserRoleID(int userRoleId, int companyID)
            {
                DataSet dsMenus = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_MenuByUserRoles_GetAllByUserRoleID", true);

                database.AddInParameter(commandWrapper, "@UserRoleID", DbType.Int32, userRoleId);
                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyID);

                try
                {
                    dsMenus = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsMenus.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class ClientsToVisitProvider
        {
            public static void Insert(int companyID, GPSTrackingDBV2.Data.TransactionManager tm, string username, string gpsImei, DateTime date, DateTime timeBegin, DateTime timeEnd, int clientGroupID, int clientID, int numPos, int distanceMts, float latitudeOrigin, float longitudeOrigin, int lastClientID, DateTime leftDateTime, DateTime arriveDateTime, DateTime dateTimeBeginClient, DateTime dateTimeEndClient)
            {

                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsToVisit_Insert", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);
                database.AddInParameter(commandWrapper, "@NumPos", DbType.Int64, numPos);
                database.AddInParameter(commandWrapper, "@ClientID", DbType.Int64, clientID);
                database.AddInParameter(commandWrapper, "@DistanceMts", DbType.Int64, distanceMts);
                database.AddInParameter(commandWrapper, "@ClientGroupID", DbType.Int64, clientGroupID);
                database.AddInParameter(commandWrapper, "@TimeBegin", DbType.DateTime, timeBegin);
                database.AddInParameter(commandWrapper, "@TimeEnd", DbType.DateTime, timeEnd);
                database.AddInParameter(commandWrapper, "@LatitudeOrigin", DbType.Double, latitudeOrigin);
                database.AddInParameter(commandWrapper, "@LongitudeOrigin", DbType.Double, longitudeOrigin);
                database.AddInParameter(commandWrapper, "@LastClientID", DbType.Int64, lastClientID);
                database.AddInParameter(commandWrapper, "@LeftDateTime", DbType.DateTime, leftDateTime);
                database.AddInParameter(commandWrapper, "@ArriveDateTime", DbType.DateTime, arriveDateTime);
                database.AddInParameter(commandWrapper, "@DateTimeBeginClient", DbType.DateTime, dateTimeBeginClient);
                database.AddInParameter(commandWrapper, "@DateTimeEndClient", DbType.DateTime, dateTimeEndClient); 

                try
                {
                    Utility.ExecuteScalar(tm, commandWrapper);
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetByUsernameByGPSImeiByDate(int companyID, string username, string gpsImei, DateTime date)
            {
                DataSet dsClientsToVisit = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsToVisit_GetByUsernameByGPSImeiByDate", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);

                try
                {
                    dsClientsToVisit = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClientsToVisit;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetAgentsEstimatedTimeReportsByUser(int companyID, string username, string gpsImei, DateTime date)
            {
                DataSet dsClientsToVisit = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "[Custom_ClientToVisit_GetAgentsEstimatedTimeReportsByUser]", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);

                try
                {
                    dsClientsToVisit = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClientsToVisit;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetAgentsRealTimeReportsByUser(int companyID, string username, string gpsImei, DateTime date)
            {
                DataSet dsClientsToVisit = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "[Custom_ClientToVisit_GetAgentsRealTimeReportsByUser]", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);

                try
                {
                    dsClientsToVisit = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClientsToVisit;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static DataSet GetAgentsAverageTimeReportsByUser(int companyID, string username, string gpsImei, DateTime dateBegin, DateTime dateEnd)
            {
                DataSet dsClientsToVisit = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "[Custom_ClientToVisit_GetAgentsAverageTimeReportsByUser]", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@DateBegin", DbType.Date, dateBegin);
                database.AddInParameter(commandWrapper, "@DateEnd", DbType.Date, dateEnd);

                try
                {
                    dsClientsToVisit = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsClientsToVisit;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public static void Delete(int companyID, GPSTrackingDBV2.Data.TransactionManager tm, string username, string gpsImei, DateTime date)
            {

                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_ClientsToVisit_Delete", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@GPSImei", DbType.String, gpsImei);
                database.AddInParameter(commandWrapper, "@Date", DbType.Date, date);

                try
                {
                    Utility.ExecuteScalar(tm, commandWrapper);
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public class RolesProvider
        {
            public static DataTable GetAllToAssociateUsers_Find(int companyID, string user, string name, bool isUser, bool isAdmin, bool isMaster)
            {
                DataSet dsRoles = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_Roles_GetAllToAssociateUsers_Find", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int64, companyID);
                database.AddInParameter(commandWrapper, "@User", DbType.String, user);

                if (!string.IsNullOrEmpty(name)) database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                if (isUser) database.AddInParameter(commandWrapper, "@IsUser", DbType.Boolean, isUser);
                if (isAdmin) database.AddInParameter(commandWrapper, "@IsAdmin", DbType.Boolean, isAdmin);
                if (isMaster) database.AddInParameter(commandWrapper, "@IsMaster", DbType.Boolean, isMaster);

                try
                {
                    dsRoles = Utility.ExecuteDataSet(database, commandWrapper);
                    return dsRoles.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }


        public class StockFilesProvider
        {
            public static int GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFiles_Get_NextID", true);

                int NextID = (int)Utility.ExecuteScalar(database, commandWrapper);

                return NextID;
            }


            public static DataTable GetOnlyFilesWithinByUsername(int companyID, string username, string name, string filename)
            {
                DataSet dataSet = new DataSet();
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFiles_Get_StockFilesByUsername", true);

                database.AddInParameter(commandWrapper, "@CompanyID", DbType.Int32, companyID);
                database.AddInParameter(commandWrapper, "@Username", DbType.String, username);
                database.AddInParameter(commandWrapper, "@Name", DbType.String, name);
                database.AddInParameter(commandWrapper, "@Filename", DbType.String, filename);

                try
                {
                    dataSet = Utility.ExecuteDataSet(database, commandWrapper);
                    return dataSet.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public class StockFilesTypeOfFileProvider
        {
            public static short GetNextID()
            {
                SqlDatabase database = new SqlDatabase(_connectionString);
                DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Custom_StockFilesTypeOfFile_Get_NextID", true);

                int NextID = (int) Utility.ExecuteScalar(database, commandWrapper);

                return Convert.ToInt16(NextID);
            }
        }                   
        
    }
    
}



